VERSION 5.00
Object = "{8E27C92E-1264-101C-8A2F-040224009C02}#7.0#0"; "MSCAL.OCX"
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Begin VB.Form frmReports 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Monthly Reports"
   ClientHeight    =   2880
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   7725
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   2880
   ScaleWidth      =   7725
   StartUpPosition =   3  'Windows Default
   Begin MSACAL.Calendar Calendar1 
      Height          =   2175
      Left            =   5160
      TabIndex        =   6
      Top             =   240
      Width           =   2295
      _Version        =   524288
      _ExtentX        =   4048
      _ExtentY        =   3836
      _StockProps     =   1
      BackColor       =   -2147483633
      Year            =   2006
      Month           =   1
      Day             =   24
      DayLength       =   1
      MonthLength     =   1
      DayFontColor    =   0
      FirstDay        =   7
      GridCellEffect  =   1
      GridFontColor   =   10485760
      GridLinesColor  =   -2147483632
      ShowDateSelectors=   -1  'True
      ShowDays        =   -1  'True
      ShowHorizontalGrid=   -1  'True
      ShowTitle       =   -1  'True
      ShowVerticalGrid=   -1  'True
      TitleFontColor  =   10485760
      ValueIsNull     =   0   'False
      BeginProperty DayFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty GridFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty TitleFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin MSACAL.Calendar Calendar2 
      Height          =   2175
      Left            =   5160
      TabIndex        =   7
      Top             =   600
      Width           =   2295
      _Version        =   524288
      _ExtentX        =   4048
      _ExtentY        =   3836
      _StockProps     =   1
      BackColor       =   -2147483633
      Year            =   2006
      Month           =   1
      Day             =   24
      DayLength       =   1
      MonthLength     =   1
      DayFontColor    =   0
      FirstDay        =   7
      GridCellEffect  =   1
      GridFontColor   =   10485760
      GridLinesColor  =   -2147483632
      ShowDateSelectors=   -1  'True
      ShowDays        =   -1  'True
      ShowHorizontalGrid=   -1  'True
      ShowTitle       =   -1  'True
      ShowVerticalGrid=   -1  'True
      TitleFontColor  =   10485760
      ValueIsNull     =   0   'False
      BeginProperty DayFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty GridFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty TitleFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin MSComDlg.CommonDialog CD2 
      Left            =   120
      Top             =   240
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin VB.CommandButton cmdGenerate 
      Caption         =   "&Generate Reports"
      Height          =   375
      Left            =   5400
      TabIndex        =   5
      Top             =   2280
      Width           =   2055
   End
   Begin VB.ComboBox cmbReports 
      Height          =   315
      Left            =   2400
      TabIndex        =   4
      Text            =   "Combo1"
      Top             =   2280
      Width           =   2775
   End
   Begin VB.TextBox Text2 
      Height          =   285
      Left            =   5520
      TabIndex        =   1
      Text            =   "Text2"
      Top             =   600
      Width           =   1935
   End
   Begin VB.TextBox Text1 
      Height          =   285
      Left            =   5520
      TabIndex        =   0
      Text            =   "Text1"
      Top             =   240
      Width           =   1935
   End
   Begin VB.Shape Shape1 
      Height          =   2655
      Left            =   120
      Top             =   120
      Width           =   7455
   End
   Begin VB.Label lblMessage3 
      Caption         =   "3"
      BeginProperty Font 
         Name            =   "Verdana"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   10
      Top             =   1440
      Width           =   7215
   End
   Begin VB.Label lblMessage2 
      Caption         =   "2"
      BeginProperty Font 
         Name            =   "Verdana"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   9
      Top             =   1200
      Width           =   7215
   End
   Begin VB.Label lblMessage1 
      Caption         =   "1"
      BeginProperty Font 
         Name            =   "Verdana"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   8
      Top             =   960
      Width           =   7215
   End
   Begin VB.Label Label2 
      Alignment       =   1  'Right Justify
      Caption         =   "End Date"
      Height          =   255
      Left            =   4200
      TabIndex        =   3
      Top             =   600
      Width           =   1095
   End
   Begin VB.Label Start 
      Alignment       =   1  'Right Justify
      Caption         =   "Start Date"
      Height          =   255
      Left            =   4200
      TabIndex        =   2
      Top             =   240
      Width           =   1095
   End
End
Attribute VB_Name = "frmReports"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
'==================================================================================
'Copyright (c) 2003, Andrew Jeremy Gargan (ajg@osite.co.za), Terence Leslie Van Zyl (tvz@osite.co.za)
'
'All rights reserved.
'
'Redistribution and use in source and binary forms, with or without modification,
'are permitted provided that the following conditions are met:
'
'Redistributions of source code must retain the above copyright notice, this list
'of conditions and the following disclaimer.
'
'Redistributions in binary form must reproduce the above copyright notice, this
'list of conditions and the following disclaimer in the documentation and/or other
'materials provided with the distribution.
'
'Neither the name of the <ORGANIZATION> nor the names of its contributors may be
'used to endorse or promote products derived from this software without specific
'prior written permission.
'
'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
'ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
'WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
'IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
'INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
'BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
'DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
'LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
'OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
'OF THE POSSIBILITY OF SUCH DAMAGE.
'===================================================================================

Private repControl As New Reportman.ReportManX

Private Sub clear_form()
    Dim lvarControl As Control
    Dim lvarMonthStart As String
    Dim lvarYearStart As String
    Dim lvarDayStart As String
    Dim lvarMonthEnd As String
    Dim lvarYearEnd As String
    Dim lvarDayEnd As String
    Dim lvarDateStart As String
    Dim lvarDateEnd As String
    Dim lvarTemp As Long
    
    For Each lvarControl In Controls
        If TypeName(lvarControl) = "TextBox" Then
            lvarControl.Text = ""
        ElseIf TypeName(lvarControl) = "CheckBox" Then
            lvarControl.Value = 0
        ElseIf TypeName(lvarControl) = "ComboBox" Then
            lvarControl.Clear
        ElseIf TypeName(lvarControl) = "ListBox" Then
            lvarControl.Clear
        ElseIf TypeName(lvarControl) = "Calendar" Then
            lvarControl.Visible = False
        ElseIf TypeName(lvarControl) = "CommandButton" Then
        End If
    Next
    
    'Guesstimate dates  :)
    lvarTemp = VBA.Month(VBA.Date)
    
    lvarDayStart = "26"
    lvarDayEnd = "25"
    lvarMonthEnd = lvarTemp
    lvarYearEnd = CStr(VBA.Year(VBA.Date))
    If lvarTemp = 1 Then
        'Previous Year
        lvarYearStart = CStr(VBA.Year(VBA.Date) - 1)
        'December
        lvarMonthStart = "12"
        '25
    Else
        lvarMonthStart = CStr(lvarTemp - 1)
        lvarYearStart = CStr(VBA.Year(VBA.Date))
    End If
    
    lvarDateStart = lvarDayStart & "-" & lvarMonthStart & "-" & lvarYearStart
    lvarDateEnd = lvarDayEnd & "-" & lvarMonthEnd & "-" & lvarYearEnd
    Text1.Text = Format(lvarDateStart, "dd-mmm-yyyy")
    Text2.Text = Format(lvarDateEnd, "dd-mmm-yyyy")
    
    'Clear Reports List
    cmbReports.Clear
    cmbReports.AddItem ("Monthly Stats")
    cmbReports.AddItem ("Monthly Statement")
    cmbReports.AddItem ("Out Standing Reminders")
    'cmbReports.AddItem ("Out Standing Reports")
    'cmbReports.AddItem ("Out Standing Authorisations")
    cmbReports.ListIndex = 0
End Sub

Private Sub Calendar2_Validate(Cancel As Boolean)
    If Calendar2.Value < Calendar1.Value Then
        Cancel = True
        MsgBox "End Date Must be >= Start Date"
    End If
End Sub

Private Sub Calendar1_Validate(Cancel As Boolean)
    If Calendar2.Value < Calendar1.Value Then
        Cancel = True
        MsgBox "End Date Must be >= Start Date"
    End If
End Sub

Private Function get_branch_name(aBRA_KEY As Integer)
    Dim lvarSQL As String
    
    lvarSQL = "SELECT BRA_NAME FROM BRANCH WHERE BRA_KEY=" & aBRA_KEY
    On Error GoTo error_handler
    get_branch_name = gvarDatabase.OpenRecordset(lvarSQL, dbOpenForwardOnly)("BRA_NAME")
    
    Exit Function
error_handler:
    On Error GoTo 0
    get_branch_name = "Error!"
End Function

Private Sub cmdGenerate_Click()
    Dim lvarRS As DAO.Recordset
    Dim lvarFSO As New FileSystemObject
    Dim lvarQuery As String
    Dim lvarKey As String
    Dim lvarPath As String
    Dim lvarPath2 As String
    Dim lvarFile As String
    
    Dim lvarSYear As String
    Dim lvarSMonth As String
    Dim lvarSDay As String
    Dim lvarEYear As String
    Dim lvarEMonth As String
    Dim lvarEDay As String
    
    Dim lvarBranchName As String
    
    Dim lvarCounter As Long
    Dim lvarTotal As Long
        
    
    lvarSYear = VBA.Year(CDate(Text1))
    lvarSMonth = VBA.MonthName(VBA.Month(CDate(Text1)), True)
    lvarSDay = VBA.Day(CDate(Text1))
    
    lvarEYear = VBA.Year(CDate(Text2))
    lvarEMonth = VBA.MonthName(VBA.Month(CDate(Text2)), True)
    lvarEDay = VBA.Day(CDate(Text2))
    
    Select Case cmbReports.Text
        
        Case "Monthly Stats" 'Stats Reports
            repControl.filename = VB.App.Path & "\stats.rep"
            repControl.SetDatabaseConnectionString "ASSESSOR", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\assessment.mdb;Persist Security Info=False"
            repControl.Preview = False
            repControl.ShowProgress = False
            
            lvarQuery = "SELECT COUNT(*) AS A_COUNT FROM (SELECT DISTINCT BRANCH.BRA_KEY FROM " & _
                        "(((((ASSESSMENT)" & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.BRA_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY)" & _
                        "INNER JOIN ASSESOR ON ASSESOR.ASO_KEY = ASSESSMENT.ASO_KEY)" & _
                        "INNER JOIN INSURANCE ON INSURANCE.INS_KEY = ASSESSMENT.INS_KEY)" & _
                        " WHERE " & _
                        "(ASS_DATE_ASSESSED >= DateValue('" & Text1 & "')) AND " & _
                        "(ASS_DATE_ASSESSED <= DateValue('" & Text2 & "')))"
            lvarTotal = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)("A_COUNT")
            
            lvarQuery = "SELECT DISTINCT BRANCH.BRA_NAME,BRANCH.BRA_KEY FROM " & _
                        "(((((ASSESSMENT)" & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.BRA_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY)" & _
                        "INNER JOIN ASSESOR ON ASSESOR.ASO_KEY = ASSESSMENT.ASO_KEY)" & _
                        "INNER JOIN INSURANCE ON INSURANCE.INS_KEY = ASSESSMENT.INS_KEY)" & _
                        " WHERE " & _
                        "(ASS_DATE_ASSESSED >= DateValue('" & Text1 & "')) AND " & _
                        "(ASS_DATE_ASSESSED <= DateValue('" & Text2 & "')) " & _
                        "ORDER BY BRANCH.BRA_NAME "
            
            Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)
            lvarCounter = 0
            Do While Not lvarRS.EOF
                repControl.SetParamValue "THE_KEY", lvarRS("BRA_KEY")
                repControl.SetParamValue "START_DATE", Text1
                repControl.SetParamValue "END_DATE", Text2
                
                lvarPath = gvarConfig.DataPath & "\" & lvarRS("BRA_NAME") & "\STATS\"
                lvarFile = lvarEYear & "-" & lvarEMonth & "-statistics" & ".pdf"
                
                lvarBranchName = get_branch_name(lvarRS("BRA_KEY"))
                
                lvarCounter = lvarCounter + 1
                lblMessage1.Caption = lvarBranchName
                lblMessage2.Caption = lvarCounter & " / " & lvarTotal & " Done"
                lblMessage3.Caption = "File: " & lvarPath & lvarFile
                
                modGlobal.Check_Dir lvarPath
               
                On Error Resume Next
                Call repControl.SaveToPDF(lvarPath & lvarFile, False)
                file_monthly lvarPath & lvarFile, VBA.Environ("HOMEDRIVE") & _
                                                    VBA.Environ("HOMEPATH") & _
                                                    "\My Documents\" & _
                                                    lvarBranchName & "-" & lvarFile
                On Error GoTo 0
                
                lvarRS.MoveNext
                DoEvents
            Loop
            MsgBox "Stats Completed"
        
        Case "Monthly Statement"
            lvarQuery = "SELECT COUNT(*) AS A_COUNT FROM (SELECT DISTINCT BRANCH.BRA_KEY FROM " & _
                        "(((((ASSESSMENT)" & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.BRA_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY)" & _
                        "INNER JOIN ASSESOR ON ASSESOR.ASO_KEY = ASSESSMENT.ASO_KEY)" & _
                        "INNER JOIN INSURANCE ON INSURANCE.INS_KEY = ASSESSMENT.INS_KEY)" & _
                        " WHERE " & _
                        "(ASS_DATE_ASSESSED >= DateValue('" & Text1 & "') AND " & _
                        "(ASS_DATE_ASSESSED <= DateValue('" & Text2 & "')) OR " & _
                        "(ASS_DATE_ASSESSED < DateValue('" & Text1 & "') AND " & _
                        " ASS_PAID = FALSE))) "
            lvarTotal = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)("A_COUNT")
            
            repControl.filename = VB.App.Path & "\statements.rep"
            repControl.Preview = False
            repControl.ShowProgress = False
            
            lvarQuery = "SELECT DISTINCT BRANCH.BRA_NAME,BRANCH.BRA_KEY FROM " & _
                        "(((((ASSESSMENT)" & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.BRA_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY)" & _
                        "INNER JOIN ASSESOR ON ASSESOR.ASO_KEY = ASSESSMENT.ASO_KEY)" & _
                        "INNER JOIN INSURANCE ON INSURANCE.INS_KEY = ASSESSMENT.INS_KEY)" & _
                        " WHERE " & _
                        "(ASS_DATE_ASSESSED >= DateValue('" & Text1 & "') AND " & _
                        "(ASS_DATE_ASSESSED <= DateValue('" & Text2 & "')) OR " & _
                        "(ASS_DATE_ASSESSED < DateValue('" & Text1 & "') AND " & _
                        " ASS_PAID = FALSE)) " & _
                        "ORDER BY BRANCH.BRA_NAME "
            
            Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)
            
            lvarCounter = 0
            Do While Not lvarRS.EOF
                'MsgBox Str(lvarRS("BRA_KEY")) & " " & Str(lvarRS("TEST")), vbOKOnly, "Test"
                repControl.SetParamValue "THE_KEY", lvarRS("BRA_KEY")
                repControl.SetParamValue "START_DATE", Text1
                repControl.SetParamValue "END_DATE", Text2
                repControl.SetParamValue "STAT_NO", Format(Text2, "mmm-yy") & "-" & Right("000" & lvarRS("BRA_KEY"), 3)
                
                lvarPath = gvarConfig.DataPath & "\" & lvarRS("BRA_NAME") & "\STATEMENTS\"
                lvarFile = lvarSYear & "-" & _
                            lvarSDay & _
                            lvarSMonth & "-" & _
                            lvarEDay & _
                            lvarEMonth & _
                            "-state.pdf"
                
                lvarCounter = lvarCounter + 1
                
                lvarBranchName = get_branch_name(lvarRS("BRA_KEY"))
                
                lblMessage1.Caption = lvarBranchName
                lblMessage2.Caption = lvarCounter & " / " & lvarTotal & " Done"
                lblMessage3.Caption = "File: " & lvarPath & lvarFile
                
                modGlobal.Check_Dir lvarPath
                
                On Error Resume Next
                Call repControl.SaveToPDF(lvarPath & lvarFile, True)
                file_monthly lvarPath & lvarFile, VBA.Environ("HOMEDRIVE") & _
                                                    VBA.Environ("HOMEPATH") & _
                                                    "\My Documents\" & _
                                                    lvarBranchName & "-" & lvarFile
                On Error GoTo 0
                
                lvarRS.MoveNext
                DoEvents
            Loop
            MsgBox "Statements Completed"
        Case "Out Standing Reminders"
            repControl.filename = VB.App.Path & "\reminder.rep"
            repControl.SetDatabaseConnectionString "ASSESSOR", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\assessment.mdb;Persist Security Info=False"
            CD2.filename = "reminders.pdf"
            CD2.ShowSave
            If CD2.filename <> "" Then
                On Error GoTo errorhandler
                Call repControl.SaveToPDF(CD2.filename, False)
                On Error GoTo 0
                'repControl.Execute
            End If
        Case "Out Standing Reports"
            lvarPath = gvarConfig.DataPath & "\UNSENT\"
            modGlobal.Check_Dir lvarPath
            
            lvarQuery = "SELECT ASSESSMENT.ASS_KEY,ASS_INVOICE_NO,ASS_RE_ASSESSMENT" & _
                        ",BRA_NAME,CLR_NAME " & _
                        "FROM (((ASSESSMENT) " & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.ASS_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY) " & _
                        "WHERE ASS_REPORTED=false " & _
                        "ORDER BY BRA_NAME"
            Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery)
            
            repControl.filename = VB.App.Path & "\report.rep"
            repControl.SetDatabaseConnectionString "ASSESSOR", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\assessment.mdb;Persist Security Info=False"
            repControl.Preview = False
            repControl.ShowPrintDialog = True
            repControl.ShowProgress = False
            
            DBEngine.BeginTrans
            Do While Not lvarRS.EOF
                lvarKey = lvarRS("ASS_KEY")
                repControl.SetParamValue "ASS_KEY", lvarKey
                lvarFile = IIf(IsNull(lvarRS("ASS_RE_ASSESSMENT")), "", IIf(lvarRS("ASS_RE_ASSESSMENT"), "RE_", "")) & lvarRS("ASS_INVOICE_NO") & ".pdf"
                Call repControl.SaveToPDF(lvarPath & lvarFile, True)
                
                lvarPath2 = gvarConfig.DataPath & "\" & lvarRS("BRA_NAME") & "\REPORT\"
                
                modGlobal.Check_Dir lvarPath2 & lvarFile
                lvarFSO.CopyFile lvarPath & lvarFile, lvarPath2 & lvarFile
                
                lvarQuery = "UPDATE ASSESSMENT SET ASS_REPORTED=true WHERE ASS_KEY=" & lvarKey
                gvarDatabase.Execute lvarQuery
            Loop
            DBEngine.CommitTrans
            MsgBox "Completed!", vbOKOnly, "Completed!"
        Case "Out Standing Authorisations"
            lvarPath = gvarConfig.DataPath & "\UNSENT\"
            modGlobal.Check_Dir lvarPath
            
            repControl.filename = VB.App.Path & "\authorise.rep"
            repControl.SetDatabaseConnectionString "ASSESSOR", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\assessment.mdb;Persist Security Info=False"
            repControl.Preview = False
            repControl.ShowPrintDialog = True
            repControl.ShowProgress = False
            
            lvarQuery = "SELECT ASSESSMENT.ASS_KEY,ASS_INVOICE_NO,ASS_RE_ASSESSMENT" & _
                        ",BRA_NAME,CLR_NAME " & _
                        "FROM (((ASSESSMENT) " & _
                        "INNER JOIN BRANCH ON BRANCH.BRA_KEY = ASSESSMENT.ASS_KEY) " & _
                        "INNER JOIN CLARK ON CLARK.CLR_KEY = ASSESSMENT.CLR_KEY) " & _
                        "WHERE ASS_DATE_AUTHORISED_SENT =false " & _
                        "ORDER BY BRA_NAME"
            Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery)
            
            DBEngine.BeginTrans
            Do While Not lvarRS.EOF
                lvarKey = lvarRS("ASS_KEY")
                repControl.SetParamValue "VAR_ASS_KEY", lvarKey
                lvarFile = IIf(IsNull(lvarRS("ASS_RE_ASSESSMENT")), "", IIf(lvarRS("ASS_RE_ASSESSMENT"), "RE_", "")) & lvarRS("ASS_INVOICE_NO") & "_auth.pdf"
                Call repControl.SaveToPDF(lvarPath & lvarFile, True)
                
                lvarPath2 = gvarConfig.DataPath & "\" & lvarRS("BRA_NAME") & "\AUTH\"
                
                modGlobal.Check_Dir lvarPath2 & lvarFile
                lvarFSO.CopyFile lvarPath & lvarFile, lvarPath2 & lvarFile
                
                lvarQuery = "UPDATE ASSESSMENT SET ASS_DATE_AUTHORISED_SENT=true WHERE ASS_KEY=" & lvarKey
                gvarDatabase.Execute lvarQuery
            Loop
            DBEngine.CommitTrans
        Case Else
            'Do diddly Squat
    End Select
    Exit Sub
errorhandler:
    If cmbReports.ListIndex > 2 Then 'Reports that
        DBEngine.CommitTrans
    End If
    On Error GoTo 0
End Sub

Private Sub Form_Load()
    
    clear_form
End Sub

Private Sub Label4_Click()

End Sub

Private Sub Text1_Click()
    Calendar1.Value = Text1
    Calendar1.Visible = True
End Sub

Private Sub Text2_Click()
    Calendar2.Value = Text2
    Calendar2.Visible = True
End Sub

Private Sub Calendar1_DblClick()
    cmdGenerate.SetFocus
End Sub

Private Sub Calendar1_LostFocus()
    Text1.Text = Format(Calendar1.Value, "dd-mmm-yyyy")
    Calendar1.Visible = False
End Sub

Private Sub Calendar2_DblClick()
    cmdGenerate.SetFocus
End Sub

Private Sub Calendar2_LostFocus()
    Calendar2.Visible = False
    Text2.Text = Format(Calendar2.Value, "dd-mmm-yyyy")
    cmdGenerate.SetFocus
End Sub

